﻿using System.Data;
using System.Data.SqlClient;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
namespace DataHelper
{
    public static class ExeclHelper
    {
        public  static void ExportExcelFile(string fileName, DataTable exportTable)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = exportTable.TableName };

                sheets.Append(sheet);
                Row headerRow = new Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in exportTable.Columns)
                {
                    columns.Add(column.ColumnName);

                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                sheetData.AppendChild(headerRow);

                foreach (DataRow dsrow in exportTable.Rows)
                {
                    Row newRow = new Row();
                    foreach (String col in columns)
                    {
                        Cell cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dsrow[col].ToString());
                        newRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(newRow);
                }
                workbookPart.Workbook.Save();
            }

        }
    }

    public static  class SqlHelper
    {

        //Install-Package System.Data.SqlClient
        public static  SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.;Initial Catalog=SheKao;Integrated Security=True";
            return conn;

        }
        //读
        public static DataSet GetDataBySQL(string strSQL)
        {
            DataSet ds = new DataSet();
            var conn = GetConnection();
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn);
            adapter.Fill(ds);
            adapter.Dispose();
            conn.Close();
            return ds;
        }
        //写更删
        public static int ExecuteNonQueryBySQL(string strSQL)
        {
            int iRows = -1;
            var conn = GetConnection();
            conn.Open();
            var cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = strSQL;
            try
            {
                iRows = cmd.ExecuteNonQuery();
            }
            catch
            {
                return -1;
            }
            cmd.Dispose();
            conn.Close();

            return iRows;
        }
    }
}
